package com.business.action.platform;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import net.sf.rose.file.excel.poi.Excel2007;
import net.sf.rose.file.util.FileUtils;
import net.sf.rose.file.util.JavaSource;
import net.sf.rose.jdbc.DBUtils;
import net.sf.rose.jdbc.PageBean;
import net.sf.rose.jdbc.bean.FieldBean;
import net.sf.rose.jdbc.bean.TableBean;
import net.sf.rose.jdbc.dao.BeanDAO;
import net.sf.rose.jdbc.dao.MapDAO;
import net.sf.rose.jdbc.query.BeanSQL;
import net.sf.rose.jdbc.query.MapSQL;
import net.sf.rose.jdbc.service.Service;
import net.sf.rose.util.RoseRuntimeException;
import net.sf.rose.util.StringUtil;
import net.sf.rose.web.util.WebUtils;

@Controller
@RequestMapping("/plat/table")
public class TableAction {

	/**
	 * 
	 */
	@ResponseBody
	@RequestMapping("/page.do")
	public PageBean page(HttpServletRequest request, Service service) {
		PageBean page = WebUtils.getPageBean(request);
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		query.setPage(page);
		query.setEntityClass(TableBean.class);
		Map<String, Object> map = WebUtils.getRequestData(request);
		//map.put("tableName", "SYS_");
		//map.put("tableName_link", "not like");
		query.createSql(map);
		dao.list();
		return page;
	}

	/**
	 * 
	 */
	@ResponseBody
	@RequestMapping("/get.do")
	public PageBean get(HttpServletRequest request, Service service, String tableID) {
		PageBean page = new PageBean();
        BeanDAO dao = new BeanDAO(service);
        BeanSQL query = dao.getQuerySQL();
        query.setEntityClass(TableBean.class);
        query.createSql(tableID);
        TableBean bean = dao.load();
		page.setData(bean);
		return page;
	}

	/**
	 * 
	 */
	@ResponseBody
	@RequestMapping("/getViewFields.do")
	public PageBean getViewFields(Service service, String sql) throws Exception {
		PageBean page = new PageBean();
		PreparedStatement pstmt;
		Connection con = service.getConnection();
		List<FieldBean> list = new ArrayList<>();
		pstmt = con.prepareStatement(sql);
		ResultSetMetaData metaData = pstmt.getMetaData();
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			FieldBean field = new FieldBean();
			field.setFieldName(metaData.getColumnName(i + 1));
			field.setJavaType(metaData.getColumnClassName(i + 1));
			field.setPropertyName(field.getFieldName());
			list.add(field);
		}
		page.setData(list);
		return page;
	}

	/**
	 * 
	 */
	@ResponseBody
	@RequestMapping("/save.do")
	public PageBean save(HttpServletRequest request, Service service, TableBean bean) {
		PageBean res = new PageBean();
		if (DBUtils.update(service, bean) > 0) {
			res.setMessage("保存信息成功！");
		} else {
			res.setSuccess(false);
			res.setMessage("保存信息失败！");
		}
		return res;
	}

	/**
	 * 
	 */
	@ResponseBody
	@RequestMapping("/saveAll.do")
	public PageBean saveAll(HttpServletRequest request, Service service, TableBean bean, String allFields) {
		PageBean res = new PageBean();
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		query.createSaveSql(bean);
		if (dao.execute()) {
			// 删除所有字段定义
			String tableID = null;//bean.getTableID();
			query.createDeleteSql(FieldBean.class, "tableID", tableID);
			dao.execute();
			List<FieldBean> array = StringUtil.parseJsonArray(FieldBean.class, allFields);
			List<FieldBean> list = new ArrayList<>();
			int index = 1;
			for (Object obj : array) {
				FieldBean f = (FieldBean) obj;
				/*f.setTableID(tableID);
				f.setSequence(index++);*/
				list.add(f);
			}
			query.createBatchInsertSql(list);
			dao.batchUpdate();
			res.setMessage("保存信息成功！");
		} else {
			res.setSuccess(false);
			res.setMessage("保存信息失败！");
		}
		return res;
	}

	/**
	 * 删除
	 */
	@ResponseBody
	@RequestMapping("/delete.do")
	public PageBean delete(HttpServletRequest request, Service service, String tableID) {
		PageBean page = new PageBean();
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		query.createDeleteSql(TableBean.class, tableID);
		if(!dao.execute()){
			page.setSuccess(false);
			page.setMessage("表结构信息表删除失败！");
			return page;
		}
		// 删除所有字段定义
		query.createDeleteSql(FieldBean.class, "tableID", tableID);
		dao.execute();
		return page;
	}

	/**
	 * 与实体类同步
	 */
	@ResponseBody
	@RequestMapping("/updateByEntity.do")
	public PageBean updateByEntity(HttpServletRequest request, Service service, String ids) {
		PageBean page = new PageBean();
		Map<String, TableBean> map = new HashMap<>();
		/*for(TableBean t :Service.getAllTableBean().values()){
			map.put(t.getTableName(), t);
		}*/
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		query.setEntityClass(TableBean.class);
		StringBuilder sb = query.updateSQL("select tableName from SYS_DEF_TABLE where tableID in(");
		for(String str : StringUtils.split(ids, ",")){
			sb.append("?,");
			query.addParameter(str);
		}
		sb.replace(sb.length() - 1, sb.length(), ")");
		List<TableBean> names = dao.list();
		for (TableBean t : names) {
			TableBean tableBean = map.get(t.getTableName());
			//service.updateNtTableBean(tableBean);
		}
		return page;
	}

	/**
	 * 与数据库同步
	 */
	@ResponseBody
	@RequestMapping("/synToDatabase.do")
	public PageBean synToDatabase(HttpServletRequest request, Service service, String ids) {
		PageBean page = new PageBean();
		// 获取表定义
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		for (String tableID : StringUtils.split(ids, ',')) {
			query.setEntityClass(TableBean.class);
			query.createSql(tableID);
			TableBean bean = dao.load();
			query.setEntityClass(FieldBean.class);
			//query.createSql("tableID", bean.getTableID());
			bean.setFields(dao.list());
			//service.initialDataBaseTable(bean);
		}
		return page;
	}

	/**
	 * 生成Java实体类
	 */
	@ResponseBody
	@RequestMapping("/createJavaBean.do")
	public PageBean createJavaBean(HttpServletRequest request, Service service, String ids) {
		PageBean page = new PageBean();
		File path = FileUtils.JAVA_SOURCE_PATH;
		JavaSource js = new JavaSource();
		// 获取表定义
		BeanDAO dao = new BeanDAO(service);
		BeanSQL query = dao.getQuerySQL();
		for (String tableID : StringUtils.split(ids, ',')) {
			query.setEntityClass(TableBean.class);
			query.createSql(tableID);
			TableBean bean = dao.load();
			query.setEntityClass(FieldBean.class);
			//query.createSql("tableID", bean.getTableID());
			bean.setFields(dao.list());
			js.build(path, bean);
			js.write();
		}
		return page;
	}

	/**
	 * 导出Excel
	 */
	@ResponseBody
	@RequestMapping("/outputExcel.do")
	public PageBean outputExcel(HttpServletRequest request, Service service, String ids) {
		PageBean page = new PageBean();
		Excel2007 excel = new Excel2007();
		excel.setTemplateFile(new File("D:\\model1.xlsx"));
		// 获取表定义
		MapDAO dao = new MapDAO(service);
		MapSQL query = dao.getQuerySQL();
		for (String tableID : StringUtils.split(ids, ',')) {
			// query.setEntityClass(TableBean.class);
			// query.createSql(tableID);
			//query.createSqlByKeyAndValue("SYS_DEF_TABLE", "tableID", tableID);
			Map<String, Object> bean = dao.load();
			// query.setEntityClass(FieldBean.class);
			//query.createSqlByKeyAndValue("SYS_DEF_FIELD", "tableID", tableID);
			bean.put("fields", dao.list());
			excel.write(new File("D:\\aa.xlsx"), bean);
		}
		return page;
	}
}
